package com.jy.modules.cims.component.sysAssist;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.context.ContextLoader;
import org.springframework.web.context.WebApplicationContext;

import com.jy.modules.cims.common.CimsConstant;
import com.jy.modules.cims.common.exception.CimsExceptionConstant;
import com.jy.modules.cims.common.exception.SysAssistException;
import com.jy.modules.cims.component.common.service.TCMmExcelDictionaryService;
import com.jy.modules.cims.component.common.service.TCMmExcelTemplateService;
import com.jy.modules.cims.data.common.dto.TCMmExcelDictionaryDTO;
import com.jy.modules.cims.data.common.dto.TCMmExcelTemplateDTO;
import com.jy.modules.cims.data.dto.LoanDetailDto;
import com.jy.modules.cims.data.dto.RefundPlanDTO;
import com.jy.platform.core.message.QueryReqBean;
import com.jy.platform.tools.excel.Template;
import com.jy.platform.tools.excel.TemplateFactory;


/*
 * 导入导出工具类
 */
@Service("com.jy.modules.cims.component.sysAssist.ExportAndImport")
public class ExportAndImport<T> {
	
	private static Logger logger = LoggerFactory.getLogger(ExportAndImport.class);

    private TCMmExcelDictionaryService serviceExcelDictionary;

    private TCMmExcelTemplateService serviceExcelTemplate;

    // service注入
    public ExportAndImport() {
        WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
        Object bean1 = wac.getBean("com.jy.modules.cims.component.common.service.TCMmExcelDictionaryService");
        this.setServiceExcelDictionary((TCMmExcelDictionaryService) bean1);

        Object bean2 = wac.getBean("com.jy.modules.cims.component.common.service.TCMmExcelTemplateService");
        this.setServiceExcelTemplate((TCMmExcelTemplateService) bean2);
    }

    /**
     * 
     * @param fileName 表格标题名
     * @param columnNames 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *        javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     *        如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     * @throws IOException
     */
    @SuppressWarnings("unchecked")
    public HSSFWorkbook exportExcel(String fileName, String contractCode, String templateType, Collection<T> dataset)
            throws IOException {
        String[] columnCodes = null;
        String[] columnNames = null;
        String isExportCostPlan = null;
        
        fileName = this.fileNameOperation(contractCode,templateType);
        try {
            
            Map<String, Object> searchParams = new HashMap<String, Object>();
            searchParams.put("contractCode", contractCode);
            searchParams.put("templateType", templateType);
            QueryReqBean params = new QueryReqBean();
            params.setSearchParams(searchParams);
            TCMmExcelTemplateDTO templateDTO = serviceExcelTemplate.findTCMmExcelTemplateByContractCodeNotDto(params
                    .getSearchParams());
            String templateCode = templateDTO.getTemplateCode();
            isExportCostPlan = templateDTO.getTemplateCode();
            TCMmExcelDictionaryDTO dtoExcelDictionaryId = serviceExcelDictionary
                    .queryTCMmExcelDictionaryByTemplateCode(templateCode);

            columnCodes = dtoExcelDictionaryId.getColumnCode().split(",");
            columnNames = dtoExcelDictionaryId.getColumnName().split(",");

        }
        catch (Exception e) {
            throw new SysAssistException(CimsExceptionConstant.EXCEL_TEMPLATE_FAIL + e.getMessage());

        }
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(fileName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        //font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.NO_FILL);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        
        HSSFFont font3 = workbook.createFont();
        //font3.setColor(HSSFColor.BLUE.index);

        /* // 声明一个画图的顶级管理器
         HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
         // 定义注释的大小和位置,详见文档
         HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
         // 设置注释内容
         comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
         // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
         comment.setAuthor("leno");*/

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(columnNames[i]);
            cell.setCellValue(text);
        }
                
     if(dataset!=null){
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            for (int i = 0; i < columnCodes.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                String fieldName = columnCodes[i];
                

                
                Object value = null;

                try {
                	value = this.objectMethod(value, t, fieldName);
                }
                catch (NoSuchMethodException e1) {
                    continue;
                }
                catch (Exception e1) {
                    throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e1.getMessage());
                }

                try {
                	this.valueFind(value,cell, workbook,font3);
                }
                catch (Exception e) {
                    throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e.getMessage());
                }
            }

        }
    }

        return workbook;

    }
    
    /**
     * 
     * @param fileName 表格标题名
     * @param columnNames 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *        javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     *        如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     * @throws IOException
     */
    @SuppressWarnings("unchecked")
	public HSSFWorkbook exportExcelCostPlan(String fileName,String projectNumber, LoanDetailDto loanList,Collection<T> dataset)
            throws IOException {
        String[] columnCodes = CimsConstant.LOAN_CODE.split(",");
        String[] columnNames = CimsConstant.LOAN_NAME.split(",");
        String[] columnCodesLoan = CimsConstant.LOAN_COST_PLAN_CODE.split(",");
        String[] columnNamesLoan = CimsConstant.LOAN_COST_PLAN_NAME.split(",");
        
        fileName = this.fileNameCostPlan(projectNumber);
        
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(fileName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        
        HSSFFont font3 = workbook.createFont();
        font3.setColor(HSSFColor.BLUE.index);

        /* // 声明一个画图的顶级管理器
         HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
         // 定义注释的大小和位置,详见文档
         HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
         // 设置注释内容
         comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
         // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
         comment.setAuthor("leno");*/

        // 产生表格标题行
        HSSFRow row = sheet.createRow(4);
        HSSFRow row1 = sheet.createRow(0);
        
        //还款计划数据title
        for (int i = 0; i < columnNames.length; i++) {
        	HSSFCell cell = row1.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(columnNames[i]);
            cell.setCellValue(text);
        }
        
        
        int indexLoan = 0;
	        indexLoan++;
	        
        row1 = sheet.createRow(indexLoan);
        
        //遍历集合数据，产生还款计划数据描述行
        for (int i = 0; i < columnCodes.length; i++) {
        	  HSSFCell cell = row1.createCell(i);                
              cell.setCellStyle(style2);
              String fieldName = columnCodes[i];

              Object value = null;

              try {
              	value = this.objectMethod(value, loanList, fieldName);
              	if(value !=null){
                  	logger.info(fieldName +"Loan----" + ":" + value);
                  }
              }
              catch (NoSuchMethodException e1) {
                  continue;
              }
              catch (Exception e1) {
                  throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e1.getMessage());
              }

              try {
              	this.valueFind(value,cell, workbook,font3);
                  
              }
              catch (Exception e) {
                  throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e.getMessage());
              }
        }
        
        //还款计划数据title
        for (int i = 0; i < columnNamesLoan.length; i++) {
        	HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(columnNamesLoan[i]);
            cell.setCellValue(text);
        }
        
        if(dataset!=null){
	        // 遍历集合数据，产生还款计划数据行
	        Iterator<T> it = dataset.iterator();
	        int index = 4;
	        
	        while (it.hasNext()) {
	            index++;
	            
	            row = sheet.createRow(index);
	            
	            T t = (T) it.next();
	            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
	            for (int i = 0; i < columnCodesLoan.length; i++) {
	                HSSFCell cell = row.createCell(i);                
	                cell.setCellStyle(style2);
	                String fieldName = columnCodesLoan[i];
	
	                Object value = null;
	
	                try {
	                	value = this.objectMethod(value, t, fieldName);
	                	if(value !=null){
	                      	logger.info(fieldName + ":" + value);
	                      }
	                }
	                catch (NoSuchMethodException e1) {
	                    continue;
	                }
	                catch (Exception e1) {
	                    throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e1.getMessage());
	                }
	
	                try {
	                	this.valueFind(value,cell, workbook,font3);
	                    
	                }
	                catch (Exception e) {
	                    throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e.getMessage());
	                }
	            }
	
	        }
        }

        return workbook;

    }
    
    private void valueFind(Object value,HSSFCell cell,HSSFWorkbook workbook,HSSFFont font3){
    	// 判断值的类型后进行强制类型转换
        String textValue = null;
        if (value instanceof Date) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
            textValue = sdf.format(date);
        } else if (null == value) {
            textValue = null;
        } else {
            // 其它数据类型都当作字符串简单处理
            textValue = value.toString();
        }
        // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
        if (textValue != null) {
            Pattern p = Pattern.compile("^//d+(//.//d+)?$");
            Matcher matcher = p.matcher(textValue);
            if (matcher.matches()) {
                // 是数字当作double处理
                cell.setCellValue(Double.parseDouble(textValue));
            } else {
                HSSFRichTextString richString = new HSSFRichTextString(textValue);
                richString.applyFont(font3);
                cell.setCellValue(richString);
            }
        }
    }
    
    private Object objectMethod(Object value,T t,String fieldName) throws Exception{
    	
    	String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
    	Class<?> tCls = t.getClass();
		Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
        value = getMethod.invoke(t, new Object[] {});
    	
    	return value;
    }
    
 private Object objectMethod(Object value,LoanDetailDto loanCostPlan ,String fieldName) throws Exception{
	 	Class clazz = loanCostPlan.getClass();
		String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
		Method getMethod = clazz.getMethod(getMethodName);
		value = getMethod.invoke(loanCostPlan);
    	return value;
    }

    /**
     * 
     * @param fileName 表格标题名
     * @param columnName 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *        javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     *        如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     * @throws IOException
     */
    @SuppressWarnings("unchecked")
    public HSSFWorkbook exportExcelSubsidiary(String fileName, String columnCode, String columnName,
            Collection<T> dataset) throws IOException {
        String[] columnCodes = null;
        String[] columnNames = null;
        try {
            if (!fileName.equals(new String(fileName.getBytes("UTF-8"), "UTF-8"))) {
                fileName = new String(fileName.getBytes("ISO-8859-1"), "UTF-8");
            }

            columnCodes = columnCode.split(",");
            columnNames = columnName.split(",");

        }
        catch (Exception e) {
            throw new SysAssistException(CimsExceptionConstant.EXCEL_TEMPLATE_FAIL + e.getMessage());

        }
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(fileName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        //font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.NO_FILL);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        
        HSSFFont font3 = workbook.createFont();
        //font3.setColor(HSSFColor.BLUE.index);

        /* // 声明一个画图的顶级管理器
         HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
         // 定义注释的大小和位置,详见文档
         HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
         // 设置注释内容
         comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
         // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
         comment.setAuthor("leno");*/

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(columnNames[i]);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        if (dataset != null) {
            Iterator<T> it = dataset.iterator();
            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                T t = (T) it.next();
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
                for (int i = 0; i < columnCodes.length; i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(style2);
                    String fieldName = columnCodes[i];
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

                    Method getMethod = null;
                    Object value = null;

                    try {
                        //map类型数据集处理
                        if (t instanceof Map) {
                            value = ((Map) t).get(fieldName.toUpperCase());
                        } else {
                            Class tCls = t.getClass();
                            getMethod = tCls.getMethod(getMethodName, new Class[] {});
                            value = getMethod.invoke(t, new Object[] {});
                        }
                    }
                    catch (NoSuchMethodException e1) {
                        continue;
                    }
                    catch (Exception e1) {
                        throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e1.getMessage());
                    }

                    try {

                    	this.valueFind(value,cell, workbook,font3);
                    }
                    catch (Exception e) {
                        throw new SysAssistException(CimsExceptionConstant.EXPORT_EXCEL_FAIL + e.getMessage());
                    }
                }
            }
        }

        return workbook;

    }

    /**
     * 导入方法
     * 
     * @param inputStream 文件数据流
     * @param fileName 导出文件名称
     * @param contractCode 合约编号
     * @param templateType 模板类型也是合约类型
     * @param classObject class类型
     * @param excelColumnsConvertMap 导入时状态类型的数据需要转换的集合例如：已卖出=02
     * @return List<?>
     * @throws Exception 
     */
    public List<?> excelImport(InputStream inputStream, String fileName, String contractCode, String templateType,
            Class<?> classObject, Map<String, Map> excelColumnsConvertMap) throws Exception {

        String[] columnCodes = null;
        String[] columnNames = null;
        String[] isImport = null;

        String extName = "xls";
        if (fileName.lastIndexOf(".") >= 0) {
            extName = fileName.substring(fileName.lastIndexOf(".") + 1);
        }

        Map<String, Object> searchParams = new HashMap<String, Object>();
        searchParams.put("contractCode", contractCode);
        searchParams.put("templateType", templateType);
        QueryReqBean params = new QueryReqBean();
        params.setSearchParams(searchParams);
        TCMmExcelTemplateDTO templateDTO = serviceExcelTemplate.findTCMmExcelTemplateByContractCodeNotDto(params
                .getSearchParams());
        String templateCode = templateDTO.getTemplateCode();
        TCMmExcelDictionaryDTO dtoExcelDictionaryId = serviceExcelDictionary
                .queryTCMmExcelDictionaryByTemplateCode(templateCode);
        isImport = dtoExcelDictionaryId.getIsImports().split(",");
        columnCodes = dtoExcelDictionaryId.getColumnCode().split(",");
        columnNames = dtoExcelDictionaryId.getColumnName().split(",");
      

        Template template = TemplateFactory.getInstance(extName);
        // 1.构建头部,fieldName同DTO中属性名对应 且索引顺序同模板列顺序一致
        ExcelHeader excelHeader = new ExcelHeader();
        List<ExcelColumns> excelColumns = new ArrayList<ExcelColumns>();
        int length = columnCodes.length;
        for (int i = 0; i < length; i++) {

            if (isImport[i].equals(CimsConstant.CREDIT_IS_IMPORT)) {
                excelColumns.add(new ExcelColumns(i, columnCodes[i], columnNames[i], true));
            } else {
                excelColumns.add(new ExcelColumns(i, columnCodes[i], columnNames[i], false));
            }
        }
        excelHeader.setColumns(excelColumns);
        // 2.设置数据开始行数和列数
        excelHeader.setColumnCount(0);
        excelHeader.setRowCount(1);
        // 3.设置总的行数
        //excelHeader.setTotalRows(51);

        excelHeader.setColumnsConvertMap(excelColumnsConvertMap);
        // 5.导入数据到内存
        return this.importData(inputStream, classObject, excelHeader,columnNames.length);
    }
    
    /**
     * 导入方法
     * 
     * @param inputStream 文件数据流
     * @param inputStream 文件数据流
     * @param fileName 导出文件名称
     * @param creditSettleBillCode 定义的模板字段 是否需要导入
     * @param creditSettleBillCode 定义的模板字段
     * @param creditSettleBillName 定义的模板字段名称
     * @param classObject class类型
     * @param excelColumnsConvertMap 导入时状态类型的数据需要转换的集合例如：已卖出=02
     * @return List<?>
     * @throws Exception 
     */
    public List<?> excelImportParameter(InputStream inputStream, String fileName, String CREDIT_SETTLEBILL_ISIMPORT,String creditSettleBillCode, String creditSettleBillName,
            Class<?> classObject, Map<String, Map> excelColumnsConvertMap) throws Exception {

        String[] columnCodes = null;
        String[] columnNames = null;
        String[] isImport = null;

        String extName = "xls";
        if (fileName.lastIndexOf(".") >= 0) {
            extName = fileName.substring(fileName.lastIndexOf(".") + 1);
        }

//        Map<String, Object> searchParams = new HashMap<String, Object>();
//        searchParams.put("contractCode", contractCode);
//        searchParams.put("templateType", templateType);
//        QueryReqBean params = new QueryReqBean();
//        params.setSearchParams(searchParams);
//        TCMmExcelTemplateDTO templateDTO = serviceExcelTemplate.findTCMmExcelTemplateByContractCodeNotDto(params
//                .getSearchParams());
//        String templateCode = templateDTO.getTemplateCode();
//        TCMmExcelDictionaryDTO dtoExcelDictionaryId = serviceExcelDictionary
//                .queryTCMmExcelDictionaryByTemplateCode(templateCode);
        isImport = CREDIT_SETTLEBILL_ISIMPORT.split(",");
        columnCodes = creditSettleBillCode.split(",");
        columnNames = creditSettleBillName.split(",");
      

        Template template = TemplateFactory.getInstance(extName);
        // 1.构建头部,fieldName同DTO中属性名对应 且索引顺序同模板列顺序一致
        ExcelHeader excelHeader = new ExcelHeader();
        List<ExcelColumns> excelColumns = new ArrayList<ExcelColumns>();
        int length = columnCodes.length;
        for (int i = 0; i < length; i++) {

            if (isImport[i].equals(CimsConstant.CREDIT_IS_IMPORT)) {
                excelColumns.add(new ExcelColumns(i, columnCodes[i], columnNames[i], true));
            } else {
                excelColumns.add(new ExcelColumns(i, columnCodes[i], columnNames[i], false));
            }
        }
        excelHeader.setColumns(excelColumns);
        // 2.设置数据开始行数和列数
        excelHeader.setColumnCount(0);
        excelHeader.setRowCount(1);
        // 3.设置总的行数
        //excelHeader.setTotalRows(51);

        excelHeader.setColumnsConvertMap(excelColumnsConvertMap);
        // 5.导入数据到内存
        return this.importData(inputStream, classObject, excelHeader,columnNames.length);
    }
    
    @SuppressWarnings("rawtypes")
	public List<?> importData(InputStream inputStream, Class clazz,
			ExcelHeader excelHeader,int headCols) throws Exception {
    	ExcelAssist helper = ExcelAssist.getInstance();
		List<List> rows = helper.excelFileConvertToList(inputStream, excelHeader.getRowCount(),excelHeader.getTotalRows(),headCols);
        Map<Integer, String> excelHeadMap = helper.convertExcelHeadToMap(excelHeader.getColumns());
        return  helper.buildDataObject(excelHeadMap, excelHeader.getColumnsConvertMap(), rows, clazz);
		
	}
    
    public String fileNameOperation(String contractCode,String templateType){
    	DateFormat format = new SimpleDateFormat("yyyyMMdd");
    	String time = format.format(new Date());
        StringBuffer fileNameDate = new StringBuffer();
        fileNameDate.append(contractCode).append(time).append(templateType);
        String fileName = fileNameDate.toString();
        return fileName;
    }
    
    public StringBuffer fileUrl(String contractCode){
    	DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
    	String time = format.format(new Date());
        StringBuffer fileNameDate = new StringBuffer();
        fileNameDate.append(contractCode).append(time);
        StringBuffer fileName = fileNameDate;
        return fileName;
    }
    
    public String fileNameCostPlan(String projectNumber){
    	DateFormat format = new SimpleDateFormat("yyyyMMdd");
    	String time = format.format(new Date());
        StringBuffer fileNameDate = new StringBuffer();
        fileNameDate.append(projectNumber).append("还款计划");
        String fileName = fileNameDate.toString();
        return fileName;
    }

    public TCMmExcelDictionaryService getServiceExcelDictionary() {
        return serviceExcelDictionary;
    }

    public void setServiceExcelDictionary(TCMmExcelDictionaryService serviceExcelDictionary) {
        this.serviceExcelDictionary = serviceExcelDictionary;
    }

    public TCMmExcelTemplateService getServiceExcelTemplate() {
        return serviceExcelTemplate;
    }

    public void setServiceExcelTemplate(TCMmExcelTemplateService serviceExcelTemplate) {
        this.serviceExcelTemplate = serviceExcelTemplate;
    }

}
